/**
	AUTHOR		: VEERU
	DATE		: OCT 26th 2005
	DESCRIPTION	: THIS PROCEDURE IS USED FOR SORT LOT DATA DUMP REPORT.
	USAGE           : EXEC SP_SORT_BIN_DUMP_REPORT '07/12/2005', '07/12/2005'	
			  IF BOTH FROM DATE AND TO DATES ARE NOT ENDTERED THEN THE INFORMTATION IS SHOWN FOR THE PAST
			  3 MONTHS
*/

ALTER   PROC SP_SORT_BIN_DUMP_REPORT
 
@FROM_DATE DATETIME,
@TO_DATE DATETIME

AS
BEGIN

	DECLARE @SORT_REL_ID 	INT
	DECLARE @SORT_LOT_ID    INT
	DECLARE @ASSM_REL_ID    INT
	DECLARE @PROCESSSTEP_ID	BIGINT
	DECLARE @STATUS		VARCHAR(50)
	DECLARE @LOT_NUMBER	VARCHAR(50)
	DECLARE @SORT_WAFER_ID	VARCHAR(300)
	DECLARE @GOOD_DICE 	INT
	DECLARE @BIN_1 		INT
	DECLARE @BIN_2 		INT
	DECLARE @BIN_3		INT
	DECLARE @BIN_4		INT
	DECLARE @BIN_5 		INT
	DECLARE @WAFER_ID 	INT
	DECLARE @YIELD 		FLOAT
	DECLARE @BIN_REJECTED   INT
	DECLARE @DATE_OUT	DATETIME

	DECLARE @SORT_LOT_WAFER_ID VARCHAR(300)
	DECLARE @ASSM_LOT_NUMBER   VARCHAR(50)
	DECLARE @ASSM_LOT_NUMBERS  VARCHAR(100)
	DECLARE @ASSM_OPN	   VARCHAR(50)
	DECLARE @ASSM_RELEASE_DATE VARCHAR(10) 
	DECLARE @ASSM_OPNS         VARCHAR(100) 

	DECLARE @WAFER_IDS_TABLE TABLE ( LOT_WAFER_ID INT )
	DECLARE @ASSM_IDS_TABLE  TABLE ( ASSM_REL_ID INT )
	DECLARE @OPN_TABLE       TABLE ( OPN VARCHAR(50) )	
	DECLARE @LOT_IDS_TABLE   TABLE ( LOT_ID INT)

	DECLARE @RESULTS TABLE ( [OPN]		 VARCHAR(50),
				 [LOT NUMBER]	 VARCHAR(50),
				 WAFER_ID	 VARCHAR(200),
			 	 [QTY AVAIL]	 INT,
			 	 [QTY PULLED]	 INT,
			 	 [STATUS]	 VARCHAR(50),
				 SORT_REL_ID 	 INT,
			 	 [SORT REL NO]	 VARCHAR(50),
			 	 [SORT REL DATE] DATETIME,	
			 	 [SORT REL PO]	 VARCHAR(50),
				 [YIELD]         FLOAT, 
			 	 PROCESSSTEP_ID	 BIGINT,
			 	 [VENDOR]	 VARCHAR(50),
				 [SORT SPEC]	 VARCHAR(100), 
				 [TEST PROG REV] VARCHAR(50), 
				 [TEST PROG NAME]VARCHAR(100), 		
				 [SORT REV]      VARCHAR(50), 		
				 [TEST FLOW]	 VARCHAR(100),			 	
			 	 [GOOD DIES] 	 INT,
			 	 [BIN 1] 	 INT,
			 	 [BIN 2] 	 INT,
			 	 [BIN 3]	 INT,
			 	 [BIN 4]	 INT,
				 [BIN 5]	 INT,
				 [BIN REJECTED]	 INT, 				 
   				 [DATE OUT]	 DATETIME,					 				
				 [ASSM LOT NO]   VARCHAR(200),
				 [ASSM OPN]	 VARCHAR(100),
				 [ASSM REL DATE] DATETIME	
				 )		

	DECLARE @RESULTS_TABLE TABLE ( 	[OPN]		VARCHAR(50),
				 	[LOT NUMBER]	VARCHAR(50),
				 	[QTY AVAIL]	INT,
				 	[QTY PULLED]	INT,
					[WAFER ID]	VARCHAR(200),
				 	[STATUS]	VARCHAR(50),
				 	[SORT REL NO]	VARCHAR(50),
				 	[SORT REL DATE]	DATETIME,	
				 	[SORT REL PO]	VARCHAR(50),
					[SORT SPEC]	VARCHAR(100), 
					[SORT REV]      VARCHAR(50), 
					[YIELD]         FLOAT, 
				 	[VENDOR]	VARCHAR(50),
					[TEST PROG REV] VARCHAR(50), 
					[TEST PROG NAME]VARCHAR(100), 					
					[TEST FLOW]	VARCHAR(100),				 	
				 	[GOOD DIES] 	INT,
				 	[BIN 1] 	INT,
				 	[BIN 2] 	INT,
				 	[BIN 3]		INT,
				 	[BIN 4]		INT,
					[BIN 5]	 	INT,
					[BIN REJECTED]	INT,
					[DATE OUT]	DATETIME,					
					[ASSM LOT NO]   VARCHAR(200),
					[ASSM OPN]	VARCHAR(100),
					[ASSM REL DATE] DATETIME )	

	DECLARE @RELEASECURSOR   CURSOR
	DECLARE @LOTCURSOR       CURSOR
	DECLARE @ASSM_REL_CURSOR CURSOR
	DECLARE @MAX_LI_DATE  DATETIME
 	DECLARE	@MIN_LI_DATE  DATETIME 	
 	DECLARE @LI_DATE_FROM DATETIME
 	DECLARE	@LI_DATE_TO   DATETIME

	SELECT @MAX_LI_DATE = MAX( CONVERT( DATETIME, SORT_REL_DATE ) ) FROM SORT_RELEASE_TABLE
 	SELECT @MIN_LI_DATE = MIN( CONVERT( DATETIME, SORT_REL_DATE ) ) FROM SORT_RELEASE_TABLE
 	
	IF ( @FROM_DATE IS NULL) AND (@TO_DATE IS NULL) 
 	BEGIN
 		SET @LI_DATE_TO = @MAX_LI_DATE 		
		SET @LI_DATE_FROM = DATEADD( MONTH, -3, @MAX_LI_DATE )
 	END
 	ELSE IF (@FROM_DATE IS NOT NULL ) AND (@TO_DATE IS NOT NULL ) 
 	BEGIN
 		SET @LI_DATE_TO = @TO_DATE
 		SET @LI_DATE_FROM = @FROM_DATE
 	END	
 	ELSE IF (@FROM_DATE IS NULL) AND (@TO_DATE IS NOT NULL ) 
 	BEGIN
 		SET @LI_DATE_TO = @TO_DATE
 		SET @LI_DATE_FROM = @MIN_LI_DATE
 	END 
  	ELSE IF (@FROM_DATE IS NOT NULL ) AND (@TO_DATE IS NULL) 
 	BEGIN
 		SET @LI_DATE_TO = @MAX_LI_DATE
 		SET @LI_DATE_FROM = @FROM_DATE	
 	END
 	ELSE
 	BEGIN
 		SET @LI_DATE_TO = @MAX_LI_DATE
 		SET @LI_DATE_FROM = @MIN_LI_DATE
 	END	

	INSERT INTO @RESULTS SELECT P.PART_NUMBER [OPN],			
		     SRLT.LOT_NUMBER [LOT NUMBER], SRLT.WAFER_ID, SRLT.QTY_AVAIL [QTY AVAIL], SRLT.QTY_PULLED [QTY PULLED], SRT.STATUS, 
                     SRT.SORT_REL_ID, SRT.SORT_REL_NUMB [SORT REL NO], SRT.SORT_REL_DATE [SORT REL DATE], SRT.SORT_REL_PO [SORT REL PO], NULL, SRT.PROCESSSTEP_ID, V_NAME [VENDOR], ST.SPEC_FILE_NAME [SORT SPEC], 
		     ST.TEST_PROGRAM_REVISION [TEST PROG REV], ST.TEST_PROGRAM_NAME [TEST PROG NAME], SRT.SORT_REVISION [SORT REV], ST.TEST_FLOW [TEST FLOW], NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
	    FROM SORT_RELEASE_TABLE SRT, SORT_RELEASE_LOT_TABLE SRLT, PRODUCT P, SPEC_TABLE ST, LOCATION LCT, VENDOR V
	    WHERE LCT.VENDOR_ID = SRT.VEND_ID AND V.VENDOR_ID = SRT.VEND_ID AND LCT.L_TYPE = 'SORT' AND ST.SPECFILE_ID = SRT.SPEC_FILE_ID 
		AND CONVERT(DATETIME, SRT.SORT_REL_DATE ) >= @LI_DATE_FROM AND CONVERT( DATETIME, SRT.SORT_REL_DATE ) <= @LI_DATE_TO 
		AND P.PROCESSSTEP_ID = SRT.PROCESSSTEP_ID AND SRLT.SORT_REL_ID = SRT.SORT_REL_ID 
		AND SRT.STATUS <> 'CANCELLED'   ORDER BY SRT.SORT_REL_NUMB 
	

	SET @RELEASECURSOR = CURSOR FOR SELECT SORT_REL_ID, [LOT NUMBER], WAFER_ID, PROCESSSTEP_ID, STATUS FROM @RESULTS
	OPEN @RELEASECURSOR
	FETCH NEXT FROM @RELEASECURSOR INTO @SORT_REL_ID, @LOT_NUMBER, @SORT_WAFER_ID, @PROCESSSTEP_ID, @STATUS
	WHILE @@FETCH_STATUS = 0
	BEGIN		
		DELETE FROM @LOT_IDS_TABLE
		INSERT INTO @LOT_IDS_TABLE SELECT LOT_ID FROM LOT_TABLE LT WHERE LT.LOT_NUMBER = @LOT_NUMBER AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID		
		IF ( @STATUS = 'CLOSED' )
		BEGIN				
			SELECT TOP 1 @SORT_LOT_ID = LOT_ID FROM @LOT_IDS_TABLE
			WHILE ( @@ROWCOUNT > 0 ) 
			BEGIN			
				IF ( (SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE WHERE LOT_ID = @SORT_LOT_ID) > 0 )
				BREAK
				DELETE FROM @LOT_IDS_TABLE WHERE LOT_ID = @SORT_LOT_ID
				SELECT TOP 1 @SORT_LOT_ID = LOT_ID FROM @LOT_IDS_TABLE
			END
		END	
		ELSE 
		BEGIN
			SELECT TOP 1 @SORT_LOT_ID = LOT_ID FROM @LOT_IDS_TABLE
		END
		DELETE FROM @WAFER_IDS_TABLE
		SET @SORT_LOT_WAFER_ID = REPLACE( @SORT_WAFER_ID , ';', ',' )
		-- TO STORE WAFER IDS IN TEMPORARY TABLE
		INSERT INTO @WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @SORT_LOT_WAFER_ID )
		
		SELECT TOP 1 @DATE_OUT = CONVERT(DATETIME , ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) ) FROM SORT_LOT_HISTORY SLH, LOT_TABLE LT 
			WHERE SLH.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @SORT_LOT_ID ORDER BY SLH.TIMESTAMP DESC		
		-- ASSEMBLY INOFRMATION FOR CLOSED SORT RELEASES
		IF ( @STATUS = 'CLOSED' )
		BEGIN				
			DELETE FROM @ASSM_IDS_TABLE
			-- TO GET ALL THE ASSEMBLY RELEASE NUMBERS IF MULTIPLE ASSEMBLY RELEASES ARE DONE FOR THE SAME SORT RELEASE
			SET @ASSM_LOT_NUMBERS = ''		
			INSERT INTO @ASSM_IDS_TABLE SELECT ARLT.ASSEMBLY_RELEASE_ID FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID AND LOT_ID = @SORT_LOT_ID AND WAFER_ID IN ( SELECT LOT_WAFER_ID FROM @WAFER_IDS_TABLE ) AND ART.STATUS <> 'CANCELLED'
	
			SELECT TOP 1 @ASSM_REL_ID = ASSM_REL_ID FROM @ASSM_IDS_TABLE
			WHILE ( @@ROWCOUNT > 0 ) 
			BEGIN
				SELECT @ASSM_LOT_NUMBER = ASSEMBLY_RELEASE_NO,  @ASSM_RELEASE_DATE = ASSEMBLY_RELEASE_DATE, @ASSM_OPN = P.PART_NUMBER FROM ASSM_RELEASE_TABLE ART, PRODUCT P WHERE P.PROCESSSTEP_ID = ART.PROCESSSTEP_ID AND ART.ASSEMBLY_RELEASE_ID = @ASSM_REL_ID				
				IF ( @ASSM_LOT_NUMBERS = '' )
				BEGIN
					SET @ASSM_LOT_NUMBERS = @ASSM_LOT_NUMBER
				END
				ELSE
				BEGIN 
					SET @ASSM_LOT_NUMBERS = @ASSM_LOT_NUMBERS +','+ @ASSM_LOT_NUMBER
				END
				INSERT INTO @OPN_TABLE SELECT P.PART_NUMBER FROM ASSM_RELEASE_TABLE ART, PRODUCT P WHERE P.PROCESSSTEP_ID = ART.PROCESSSTEP_ID AND ART.ASSEMBLY_RELEASE_ID = @ASSM_REL_ID				
				DELETE FROM @ASSM_IDS_TABLE WHERE  ASSM_REL_ID = @ASSM_REL_ID 
				SET @ASSM_REL_ID = NULL
				SET @ASSM_LOT_NUMBER = NULL							
				SELECT TOP 1 @ASSM_REL_ID = ASSM_REL_ID FROM @ASSM_IDS_TABLE
			END
			-- TO GET ALL THE OPNS FOR THE MULTIPLE ASSEMBLY RELEASES 
			SET @ASSM_OPNS = ''
			SELECT TOP 1 @ASSM_OPN = OPN FROM @OPN_TABLE
			WHILE ( @@ROWCOUNT > 0)
			BEGIN
				IF ( @ASSM_OPNS = '' )
				BEGIN
					SET @ASSM_OPNS = @ASSM_OPN
				END
				ELSE
				BEGIN 
					SET @ASSM_OPNS = @ASSM_OPNS +','+ @ASSM_OPN
				END	
				DELETE FROM @OPN_TABLE WHERE OPN = @ASSM_OPN
				SELECT TOP 1 @ASSM_OPN = OPN FROM @OPN_TABLE 
			END			
		END	
		-- TO GET THE BINNING INFORMATION 
		SELECT @GOOD_DICE= SUM( GOOD_DICE ), @BIN_1 = SUM( ISNULL ( BIN_1, 0 ) ), @BIN_2 = SUM( ISNULL ( BIN_2, 0 ) ), @BIN_3 = SUM( ISNULL ( BIN_3, 0 )), @BIN_4 = SUM( ISNULL ( BIN_4, 0 )), @BIN_5 = SUM( ISNULL ( BIN_5, 0 ) ),
		@YIELD = AVG( YIELD ), @BIN_REJECTED = SUM( BIN_REJECTED ) 
		FROM INFORMATIVE_BINNING IB,LOT_TABLE LT WHERE IB.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @SORT_LOT_ID AND WAFER_ID IN ( SELECT LOT_WAFER_ID FROM @WAFER_IDS_TABLE ) 
		IF ( @GOOD_DICE IS NULL )
		BEGIN			
			SELECT @GOOD_DICE= SUM( GOOD_DICE ), @BIN_1 = SUM( ISNULL ( BIN_1, 0 ) ), @BIN_2 = SUM( ISNULL ( BIN_2, 0 ) ), @BIN_3 = SUM( ISNULL ( BIN_3, 0 )), @BIN_4 = SUM( ISNULL ( BIN_4, 0 )), @BIN_5 = SUM( ISNULL ( BIN_5, 0 ) ),
			@YIELD = AVG( YIELD ), @BIN_REJECTED = SUM( BIN_REJECTED ) 
			FROM INFORMATIVE_BINNING_HISTORY IB,LOT_TABLE LT WHERE IB.LOT_ID = LT.LOT_ID AND LT.LOT_ID = @SORT_LOT_ID AND WAFER_ID IN ( SELECT LOT_WAFER_ID FROM @WAFER_IDS_TABLE ) 		
		END		
		-- TO GET THE BINNING INFORMATION FROM HISTORY TABLE IF IT IS NOT FOUND IN MASTER TABLE
		INSERT INTO @RESULTS_TABLE
			SELECT [OPN], [LOT NUMBER], [QTY AVAIL], [QTY PULLED], @SORT_LOT_WAFER_ID, [STATUS], [SORT REL NO], [SORT REL DATE], [SORT REL PO], [SORT SPEC], [SORT REV], @YIELD, [VENDOR], [TEST PROG REV], [TEST PROG NAME], [TEST FLOW], @GOOD_DICE, @BIN_1, @BIN_2, @BIN_3, @BIN_4, @BIN_5, @BIN_REJECTED, @DATE_OUT, @ASSM_LOT_NUMBERS ,@ASSM_OPNS,@ASSM_RELEASE_DATE 	
			FROM @RESULTS WHERE SORT_REL_ID = @SORT_REL_ID						
	
		SET @WAFER_ID		= NULL
		SET @GOOD_DICE		= NULL
		SET @BIN_1  		= NULL
		SET @BIN_2 		= NULL
		SET @BIN_3 		= NULL
		SET @BIN_4 		= NULL
		SET @BIN_5 		= NULL
		SET @YIELD 		= NULL
		SET @BIN_REJECTED	= NULL
		SET @DATE_OUT 		= NULL
		SET @SORT_REL_ID 	= NULL
		SET @WAFER_ID 		= NULL
		SET @SORT_LOT_WAFER_ID 	= NULL
		SET @SORT_LOT_ID 	= NULL
		SET @ASSM_LOT_NUMBER 	= NULL
		SET @ASSM_OPN 		= NULL
		SET @ASSM_RELEASE_DATE 	= NULL
		SET @ASSM_LOT_NUMBERS	= NULL
		SET @ASSM_OPNS		= NULL	
		SET @LOT_NUMBER		= NULL	
		SET @SORT_WAFER_ID	= NULL	
		SET @PROCESSSTEP_ID 	= NULL	
		SET @STATUS		= NULL	

		FETCH NEXT FROM @RELEASECURSOR INTO @SORT_REL_ID, @LOT_NUMBER, @SORT_WAFER_ID,@PROCESSSTEP_ID, @STATUS
	END
	CLOSE @RELEASECURSOR
	DEALLOCATE @RELEASECURSOR 	
	
	SELECT * FROM @RESULTS_TABLE ORDER BY [SORT REL DATE] DESC, [SORT REL NO] DESC
END
